package io.paper.service.impl;

import java.io.IOException;
import java.io.OutputStream;
import java.sql.Connection;
import java.sql.SQLException;
import java.text.MessageFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.log4j.Logger;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.springframework.stereotype.Service;

import com.alibaba.druid.util.StringUtils;

import interbase.interclient.PreparedStatement;
import interbase.interclient.ResultSet;
import io.paper.entity.BeerMoldEntity;
import io.paper.service.BeerMoldService;
import io.paper.utils.CodeSetUtil;
import io.renren.utils.util.DatabaseUtil;
import io.renren.utils.util.exception.XMTException;



/**
 * 系统用户
 * 
 * @author chenshun
 * @email sunlightcs@gmail.com
 * @date 2016年9月18日 上午9:46:09
 */
@Service("beerMoldService")
public class BeerMoldServiceImpl implements BeerMoldService {

	private static Logger logger = Logger.getLogger(BeerMoldServiceImpl.class);
			
	@Override
	public List<BeerMoldEntity> queryBeerList() {
		Connection con = null;
		String sql = "select * from BEERMOLD ";
        PreparedStatement pstmt;
        List<BeerMoldEntity> result = new ArrayList<BeerMoldEntity>();
		try {
			logger.info("queryBeerList:"+sql);
			con = DatabaseUtil.getConnection("paper");
			pstmt = (PreparedStatement)con.prepareStatement(CodeSetUtil.getGBKtoISO(sql));
            ResultSet rs = (ResultSet) pstmt.executeQuery();
            while (rs.next()) {
            	BeerMoldEntity vo = new BeerMoldEntity();
            	vo.setBEERMOLDID(CodeSetUtil.getISOtoGBK(rs.getString("BEERMOLDID")));
            	vo.setBEERMOLDNAME(CodeSetUtil.getISOtoGBK(rs.getString("BEERMOLDNAME")));
            	vo.setBEERMOLDLEVEL(rs.getInt("BEERMOLDLEVEL"));
            	vo.setCUSTOMERCODE(CodeSetUtil.getISOtoGBK(rs.getString("CUSTOMERCODE")));
            	vo.setCUSTOMERID(CodeSetUtil.getISOtoGBK(rs.getString("CUSTOMERID")));
            	vo.setPARENTBEERMOLDID(CodeSetUtil.getISOtoGBK(rs.getString("PARENTBEERMOLDID")));
            	vo.setPLACE(CodeSetUtil.getISOtoGBK(rs.getString("PLACE")));
            	vo.setQUALITY(CodeSetUtil.getISOtoGBK(rs.getString("QUALITY")));
            	vo.setQUANTITY(rs.getDouble("QUANTITY"));
            	vo.setUNITEXCH(rs.getDouble("UNITEXCH"));
            	vo.setUNITID(CodeSetUtil.getISOtoGBK(rs.getString("UNITID")));
            	vo.setUNITQUANTITY(rs.getInt("UNITQUANTITY"));
            	vo.setUSELENGTH(rs.getDouble("USELENGTH"));
            	vo.setUSEWIDTH(rs.getDouble("USEWIDTH"));
            	vo.setWEIGHT(rs.getDouble("WEIGHT"));
            	vo.setUSELENGTH(rs.getDouble("USELENGTH"));
            	vo.setUSEWIDTH(rs.getDouble("USEWIDTH"));
            	result.add(vo);
            }
		} catch (XMTException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
            try {
                if (con != null) {
                    con.close();
                    con = null;
                }
            } catch (Exception exception) {
            	exception.printStackTrace();
            }
        }
		return result;
	}

	@Override
	public List<BeerMoldEntity> queryList(Map<String, Object> map) {
		Connection con = null;
		String startRow = map.get("startRow")+"" ;
		String endRow = map.get("endRow")+"" ;
		BeerMoldEntity param = (BeerMoldEntity) map.get("para");
		String bEERMOLDID= param.getBEERMOLDID();
		String cUSTOMERCODE = param.getCUSTOMERCODE();
		String sql = "select * from BEERMOLD where  1=1 ";
		if(!StringUtils.isEmpty(bEERMOLDID)){
			sql+="and BEERMOLDID like '%"+bEERMOLDID+"%' ";
		}
		if(!StringUtils.isEmpty(cUSTOMERCODE)){
			sql+="and CUSTOMERID like '%"+cUSTOMERCODE+"%' ";
		}
		sql+= "order by BEERMOLDID asc ";
		sql+= "rows #{startRow} to #{endRow} ";
		sql=CodeSetUtil.getDbParam(sql, startRow,"startRow");
		sql=CodeSetUtil.getDbParam(sql, endRow,"endRow");
        PreparedStatement pstmt;
        List<BeerMoldEntity> result = new ArrayList<BeerMoldEntity>();
		try {
			logger.info("queryList:"+sql);
			con = DatabaseUtil.getConnection("paper");
			pstmt = (PreparedStatement)con.prepareStatement(CodeSetUtil.getGBKtoISO(sql));
            ResultSet rs = (ResultSet) pstmt.executeQuery();
            while (rs.next()) {
            	BeerMoldEntity vo = new BeerMoldEntity();
            	vo.setBEERMOLDID(CodeSetUtil.getISOtoGBK(rs.getString("BEERMOLDID")));
            	vo.setBEERMOLDNAME(CodeSetUtil.getISOtoGBK(rs.getString("BEERMOLDNAME")));
            	vo.setBEERMOLDLEVEL(rs.getInt("BEERMOLDLEVEL"));
            	vo.setCUSTOMERCODE(CodeSetUtil.getISOtoGBK(rs.getString("CUSTOMERCODE")));
            	vo.setCUSTOMERID(CodeSetUtil.getISOtoGBK(rs.getString("CUSTOMERID")));
            	vo.setPARENTBEERMOLDID(CodeSetUtil.getISOtoGBK(rs.getString("PARENTBEERMOLDID")));
            	vo.setPLACE(CodeSetUtil.getISOtoGBK(rs.getString("PLACE")));
            	vo.setQUALITY(CodeSetUtil.getISOtoGBK(rs.getString("QUALITY")));
            	vo.setQUANTITY(rs.getDouble("QUANTITY"));
            	vo.setUNITEXCH(rs.getDouble("UNITEXCH"));
            	vo.setUNITID(CodeSetUtil.getISOtoGBK(rs.getString("UNITID")));
            	vo.setUNITQUANTITY(rs.getInt("UNITQUANTITY"));
            	vo.setUSELENGTH(rs.getDouble("USELENGTH"));
            	vo.setUSEWIDTH(rs.getDouble("USEWIDTH"));
            	vo.setWEIGHT(rs.getDouble("WEIGHT"));
            	vo.setUSELENGTH(rs.getDouble("USELENGTH"));
            	vo.setUSEWIDTH(rs.getDouble("USEWIDTH"));
            	vo.setOldDataFlag(true);
            	result.add(vo);
            }
		} catch (XMTException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
            try {
                if (con != null) {
                    con.close();
                    con = null;
                }
            } catch (Exception exception) {
            	exception.printStackTrace();
            }
        }
		return result;
	}

	@Override
	public int queryTotal(Map<String, Object> map) {
		Connection con = null;
		BeerMoldEntity param = (BeerMoldEntity) map.get("para");
		String bEERMOLDID= param.getBEERMOLDID();
		String cUSTOMERCODE = param.getCUSTOMERCODE();
		String sql = "select count(0) as totalNum from BEERMOLD where  1=1";
		if(!StringUtils.isEmpty(bEERMOLDID)){
			sql+="and BEERMOLDID like '%"+bEERMOLDID+"%' ";
		}
		if(!StringUtils.isEmpty(cUSTOMERCODE)){
			sql+="and CUSTOMERID like '%"+cUSTOMERCODE+"%' ";
		}
        PreparedStatement pstmt;
        int totalNum=0;
		try {
			con = DatabaseUtil.getConnection("paper");
			pstmt = (PreparedStatement)con.prepareStatement(CodeSetUtil.getGBKtoISO(sql));
            ResultSet rs = (ResultSet) pstmt.executeQuery();
            while (rs.next()) {
            	totalNum=rs.getInt("totalNum");
            }
		} catch (XMTException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
            try {
                if (con != null) {
                    con.close();
                    con = null;
                }
            } catch (Exception exception) {
            	exception.printStackTrace();
            }
        }
		return totalNum;
	}

	@Override
	public void deleteBatch(String[] bEERMOLDIDs) {
		// TODO Auto-generated method stub
		System.out.println(bEERMOLDIDs);
		Connection con = null;
		String sql = "delete from BEERMOLD where BEERMOLDID in ({0}) ";
		PreparedStatement pstmt;
		try {
			String param="";
			for(String str:bEERMOLDIDs){
				param+="'"+str+"',";
			}
			if(!StringUtils.isEmpty(param)){
				param=param.substring(0, param.length()-1);
			}
			String formatsql = MessageFormat.format(sql,param);
			logger.info("delete: "+formatsql);
			con = DatabaseUtil.getConnection("paper");
			pstmt = (PreparedStatement)con.prepareStatement(CodeSetUtil.getGBKtoISO(formatsql));
            pstmt.execute();
		} catch (XMTException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
            try {
                if (con != null) {
                    con.close();
                    con = null;
                }
            } catch (Exception exception) {
            	exception.printStackTrace();
            }
        }
	}

	@Override
	public void save(BeerMoldEntity params) {
		// TODO Auto-generated method stub
		Connection con = null;
		String sql = "insert into BEERMOLD (BEERMOLDID,BEERMOLDNAME,CUSTOMERID,CUSTOMERCODE,QUALITY,"
				+ "USELENGTH,USEWIDTH,UNITID,UNITEXCH,QUANTITY,PLACE,BEERMOLDLEVEL,PARENTBEERMOLDID,"
				+ "UNITQUANTITY,WEIGHT)values ({0},{1},{2},{3},{4},{5},{6},{7},{8},{9},{10},{11},{12},{13},{14}) ";
		String formatsql = MessageFormat.format(sql, "'"+params.getBEERMOLDID()+"'","'"+params.getBEERMOLDNAME()+"'",
				"'"+params.getCUSTOMERID()+"'","'"+params.getCUSTOMERCODE()+"'","'"+params.getQUALITY()+"'",params.getUSELENGTH(),
				params.getUSEWIDTH(),"'"+params.getUNITID()+"'",params.getUNITEXCH(),
				params.getQUANTITY(),"'"+params.getPLACE()+"'",params.getBEERMOLDLEVEL(),"'"+params.getPARENTBEERMOLDID()+"'",
				params.getUNITQUANTITY(),params.getWEIGHT());
		PreparedStatement pstmt;
		try {
			logger.info("save: "+formatsql);
			con = DatabaseUtil.getConnection("paper");
			pstmt = (PreparedStatement)con.prepareStatement(CodeSetUtil.getGBKtoISO(formatsql));
            pstmt.execute();
		} catch (XMTException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
            try {
                if (con != null) {
                    con.close();
                    con = null;
                }
            } catch (Exception exception) {
            	exception.printStackTrace();
            }
        }
	}

	@Override
	public void update(BeerMoldEntity params) {
		// TODO Auto-generated method stub
		if(null!=params || !StringUtils.isEmpty(params.getBEERMOLDID())){
			Connection con = null;
			StringBuffer sql = new StringBuffer("update BEERMOLD t set t.BEERMOLDID='"+params.getBEERMOLDID()+"'");
			if(null!=params.getBEERMOLDNAME()){
				sql.append(" ,t.BEERMOLDNAME='"+params.getBEERMOLDNAME()+"'");
			}
			if(null!=params.getCUSTOMERID()){
				sql.append(" ,t.CUSTOMERID='"+params.getCUSTOMERID()+"'");
			}
			if(null!=params.getCUSTOMERCODE()){
				sql.append(" ,t.CUSTOMERCODE='"+params.getCUSTOMERCODE()+"'");
			}
			if(null!=params.getQUALITY()){
				sql.append(" ,t.QUALITY='"+params.getQUALITY()+"'");
			}
			if(null!=params.getUSELENGTH()){
				sql.append(" ,t.USELENGTH="+params.getUSELENGTH());
			}
			if(null!=params.getUSEWIDTH()){
				sql.append(" ,t.USEWIDTH="+params.getUSEWIDTH());
			}
			if(null!=params.getUNITID()){
				sql.append(" ,t.UNITID='"+params.getUNITID()+"'");
			}
			if(null!=params.getUNITEXCH()){
				sql.append(" ,t.UNITEXCH="+params.getUNITEXCH());
			}
			if(null!=params.getQUANTITY()){
				sql.append(" ,t.QUANTITY="+params.getQUANTITY());
			}
			if(null!=params.getPLACE()){
				sql.append(" ,t.PLACE='"+params.getPLACE()+"'");
			}
			if(null!=params.getBEERMOLDLEVEL()){
				sql.append(" ,t.BEERMOLDLEVEL="+params.getBEERMOLDLEVEL());
			}
			if(null!=params.getPARENTBEERMOLDID()){
				sql.append(" ,t.PARENTBEERMOLDID='"+params.getPARENTBEERMOLDID()+"'");
			}
			if(null!=params.getUNITQUANTITY()){
				sql.append(" ,t.UNITQUANTITY="+params.getUNITQUANTITY());
			}
			if(null!=params.getWEIGHT()){
				sql.append(" ,t.WEIGHT="+params.getWEIGHT());
			}
			
			sql.append(" where t.BEERMOLDID='"+params.getBEERMOLDID()+"'");
			PreparedStatement pstmt;
			try {
				logger.info("update:"+sql.toString());
				con = DatabaseUtil.getConnection("paper");
				pstmt = (PreparedStatement)con.prepareStatement(CodeSetUtil.getGBKtoISO(sql.toString()));
				int rs = pstmt.executeUpdate();
			} catch (XMTException e) {
				e.printStackTrace();
			} catch (SQLException e) {
				e.printStackTrace();
			}finally {
	            try {
	                if (con != null) {
	                    con.close();
	                    con = null;
	                }
	            } catch (Exception exception) {
	            	exception.printStackTrace();
	            }
	        }
		}
		
	}

	@Override
	public Integer importBeerMold(List<BeerMoldEntity> params) {
		int num=0;
		for(BeerMoldEntity beerMoldVO:params){
			//判断是否存在
			BeerMoldEntity beerMoldparam = new BeerMoldEntity();
			beerMoldparam.setBEERMOLDID(beerMoldVO.getBEERMOLDID());
			Map<String, Object> map = new HashMap<String,Object>();
			map.put("para", beerMoldparam);
			int countNum = queryTotal(map);
			if(countNum==0){
				//新增
				save(beerMoldVO);
			}else{
				//修改
				update(beerMoldVO);
			}
			num++;
		}
		return num;
	}

	@Override
	public boolean exportBeerMoldExcel(Map<String, Object> paramMap, OutputStream out) {
		int totalRecord = queryTotal(paramMap);
		int page_size=100;
		// 第一步，创建一个webbook，对应一个Excel文件  
		HSSFWorkbook  wb = new HSSFWorkbook ();  
		try {
	        // 第二步，在webbook中添加一个sheet,对应Excel文件中的sheet  
			Sheet sheet = wb.createSheet(); 
	        sheet.setColumnWidth(0,10*2*256);
	        sheet.setColumnWidth(1,10*2*256);
	        sheet.setColumnWidth(2,10*2*256);
	        sheet.setColumnWidth(3,10*2*256);
	        sheet.setColumnWidth(4,10*2*256);
	        sheet.setColumnWidth(5,10*2*256);
	        sheet.setColumnWidth(6,10*2*256);
	        sheet.setColumnWidth(7,10*2*256);
	        sheet.setColumnWidth(8,10*2*256);
	        sheet.setColumnWidth(9,10*2*256);
	        sheet.setColumnWidth(10,10*2*256);
	        sheet.setColumnWidth(11,10*2*256);
	        sheet.setColumnWidth(12,10*2*256);
	        // 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short  
	        //字体样式
			Font fontTitle = wb.createFont();
			fontTitle.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);//粗体显示
			//fontTitle.setColor(HSSFColor.WHITE.index);
			//创建标题样式
			CellStyle styleTitle = wb.createCellStyle();
			styleTitle.setFont(fontTitle);
			styleTitle.setFillBackgroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
			styleTitle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
			styleTitle.setFillPattern(CellStyle.SOLID_FOREGROUND);
			//创建数据块样式
			CellStyle whiteContent = wb.createCellStyle();
			whiteContent.setFillPattern(CellStyle.SOLID_FOREGROUND);
			//设置标题行
			Row rowTitle = sheet.createRow(0); //行
			Cell cellTitle = null;
			//啤模编号
			cellTitle = rowTitle.createCell((short) 0);
			cellTitle.setCellValue("啤模编号");
			cellTitle.setCellStyle(styleTitle);
			//啤模名称
			cellTitle = rowTitle.createCell((short) 1);
			cellTitle.setCellValue("啤模名称");
			cellTitle.setCellStyle(styleTitle);
			//客户编号
			cellTitle = rowTitle.createCell((short) 2);
			cellTitle.setCellValue("客户编号");
			cellTitle.setCellStyle(styleTitle);
			//客户名称
			cellTitle = rowTitle.createCell((short) 3);
			cellTitle.setCellValue("客户名称");
			cellTitle.setCellStyle(styleTitle);
			//纸质
			cellTitle = rowTitle.createCell((short) 4);
			cellTitle.setCellValue("纸质");
			cellTitle.setCellStyle(styleTitle);
			//用料(宽)
			cellTitle = rowTitle.createCell((short) 5);
			cellTitle.setCellValue("用料(宽)");
			cellTitle.setCellStyle(styleTitle);
			//用料(长)
			cellTitle = rowTitle.createCell((short) 6);
			cellTitle.setCellValue("用料(长)");
			cellTitle.setCellStyle(styleTitle);
			//单位
			cellTitle = rowTitle.createCell((short) 7);
			cellTitle.setCellValue("单位");
			cellTitle.setCellStyle(styleTitle);
			//单位比率
			cellTitle = rowTitle.createCell((short) 8);
			cellTitle.setCellValue("单位比率");
			cellTitle.setCellStyle(styleTitle);
			//一模成型数量
			cellTitle = rowTitle.createCell((short) 9);
			cellTitle.setCellValue("一模成型数量");
			cellTitle.setCellStyle(styleTitle);
			//每箱用料比例
			cellTitle = rowTitle.createCell((short) 10);
			cellTitle.setCellValue("每箱用料比例");
			cellTitle.setCellStyle(styleTitle);
			//存放位置
			cellTitle = rowTitle.createCell((short) 11);
			cellTitle.setCellValue("存放位置");
			cellTitle.setCellStyle(styleTitle);
			//组件编号
			cellTitle = rowTitle.createCell((short) 12);
			cellTitle.setCellValue("组件编号");
			cellTitle.setCellStyle(styleTitle);
	        int num = 0;
	        Cell cell = null;
	        int export_times = totalRecord % page_size > 0 ? totalRecord / page_size
	    			+ 1 : totalRecord / page_size;
	    	// 按次数将数据写入文件
	        List<BeerMoldEntity>  dtoList=new ArrayList<BeerMoldEntity>();
	    	for (int j = 0; j < export_times; j++) {
	    		//参数
	    		paramMap.put("startRow", j*page_size+1);
	    		paramMap.put("endRow", (j+1)*page_size);
	    		dtoList= queryList(paramMap);
	    		int len = dtoList.size() < page_size ? dtoList.size() : page_size;
	    		for (int i = 0; i < len; i++){
		        	num ++;
		        	BeerMoldEntity dto = dtoList.get(i);
		        	//SO基本信息
		        	num = setWxInfoRow(sheet, cell, whiteContent, num, dto);
		        }
	    		dtoList.clear(); // 每次存储len行，用完了将内容清空，以便内存可重复利用
	    	}
	        //写入输出流
	        wb.write(out);
		} catch (Exception e) {
			e.printStackTrace();
			logger.info("导出啤模："+e.getMessage());
			return false;
		} finally{
			try {
				out.close();
				wb.close();
			} catch (IOException e) {
				e.printStackTrace();
			}
		}
		return true;
	}
	
	/**
	 * 设置SO基本信息行
	 */
	@SuppressWarnings("deprecation")
	public int setWxInfoRow(Sheet sheet, Cell cell, CellStyle styleTitle, int rowNum, BeerMoldEntity dto){
		try {
			Row row = sheet.createRow(rowNum); //行
			//啤模编号
			String BEERMOLDID = dto.getBEERMOLDID();
			cell = row.createCell((short) 0);
			cell.setCellValue(null == BEERMOLDID ? "" : BEERMOLDID);
			//啤模名称
			String BEERMOLDNAME = dto.getBEERMOLDNAME();
			cell = row.createCell((short) 1);
			cell.setCellValue(null==BEERMOLDNAME?"":BEERMOLDNAME);
			//客户编号
			String CUSTOMERID = dto.getCUSTOMERID();
			cell = row.createCell((short) 2);
			cell.setCellValue(null==CUSTOMERID?"":CUSTOMERID);
			//客户名称
			String CUSTOMERCODE = dto.getCUSTOMERCODE();
			cell = row.createCell((short) 3);
			cell.setCellValue(null==CUSTOMERCODE?"":CUSTOMERCODE);
			//纸质
			String QUALITY = dto.getQUALITY();
			cell = row.createCell((short) 4);
			cell.setCellValue(null==QUALITY?"":QUALITY);
			//用料(宽)
			Double USEWIDTH = dto.getUSEWIDTH();
			cell = row.createCell((short) 5);
			cell.setCellValue(null==USEWIDTH?"":(USEWIDTH.toString()));
			//用料(长)
			Double USELENGTH = dto.getUSELENGTH();
			cell = row.createCell((short) 6);
			cell.setCellValue(null==USELENGTH?"":(USELENGTH.toString()));
			//单位
			String UNITID = dto.getUNITID();
			cell = row.createCell((short) 7);
			cell.setCellValue(null==UNITID?"":UNITID);
			//单位比率
			Double UNITEXCH = dto.getUNITEXCH();
			cell = row.createCell((short) 8);
			cell.setCellValue(null==UNITEXCH?"":(UNITEXCH.toString()));
			//一模成型数量
			Double QUANTITY = dto.getQUANTITY();
			cell = row.createCell((short) 9);
			cell.setCellValue(null==QUANTITY?"":(QUANTITY.toString()));
			//每箱用料比例
			Integer uNITQUANTITY = dto.getUNITQUANTITY();
			cell = row.createCell((short) 10);
			cell.setCellValue(null==uNITQUANTITY?"":(uNITQUANTITY.toString()));
			//存放位置
			String PLACE = dto.getPLACE();
			cell = row.createCell((short) 11);
			cell.setCellValue(null==PLACE?"":PLACE);
			//组件编号
			String pARENTBEERMOLDID = dto.getPARENTBEERMOLDID();
			cell = row.createCell((short) 12);
			cell.setCellValue(null==pARENTBEERMOLDID?"":pARENTBEERMOLDID);
			
		} catch (Exception e) {
			e.printStackTrace();
			logger.info("导出数据组装错误：Error "+e.getMessage());
		}
		return rowNum;
	}
	
	
}
